package com.stud.common.jdbc;

import com.github.pagehelper.Page;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.stud.common.utils.LogUtils;
import com.stud.common.utils.PageUtils;
import com.stud.common.utils.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.lang.Nullable;
import org.springframework.util.CollectionUtils;

import java.sql.PreparedStatement;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * @author Stud
 * @title XXXXX
 * @desription XXXXX
 * @data 2022-09-19 8:58
 */
public class MyJdbcTemplate extends JdbcTemplate {

    public <T> T find(String sql, Class<T> clazz, Object... params) {
        LogUtils.realSqlLog(sql, params);
        List<T> result = super.query(sql, new BeanPropertyRowMapper<>(clazz), params);
        if (CollectionUtils.isEmpty(result)) {
            return null;
        }
        return result.get(0);
    }

    /**
     * 查找单个对象 查到多条结果返回第一条
     *
     * @param sql
     * @param params
     * @return Map
     */
    public Map find(String sql, Object... params) {
        LogUtils.realSqlLog(sql, params);
        List<Map<String, Object>> result = super.queryForList(sql, params);
        if (CollectionUtils.isEmpty(result)) {
            return null;
        }
        return result.get(0);
    }

    /**
     * 查找对象列表
     *
     * @param sql
     * @param params
     * @return
     */
    public <T> List<Map<String, T>> select2Map(String sql, Object... params) {
        LogUtils.realSqlLog(sql, params);
        List<Map<String, T>> list = Lists.newArrayList();
        SqlRowSet sqlRowSet = super.queryForRowSet(sql, params);
        String[] columnNames = sqlRowSet.getMetaData().getColumnNames();
        while (sqlRowSet.next()) {
            Map<String, T> row = Maps.newHashMap();
            for (String columnName : columnNames) {
                row.put(columnName, (T) sqlRowSet.getString(columnName));
            }
            list.add(row);
        }
        return list;
    }

    /**
     * 查找对象列表
     *
     * @param sql
     * @param clazz
     * @param params
     * @param <T>
     * @return
     */
    public <T> List<T> select(String sql, Class<T> clazz, List params) {
        return select(sql, clazz, params.toArray());
    }

    /**
     * 查找对象列表
     *
     * @param sql
     * @param clazz
     * @param params
     * @param <T>
     * @return
     */
    public <T> List<T> select(String sql, Class<T> clazz, Object... params) {
        LogUtils.realSqlLog(sql, params);
        if (PageUtils.isStart()) {
            try {
                Page<T> page = new Page<>();
                sql = sql.trim();
                if (sql.endsWith(";")) {
                    sql = sql.substring(sql.length() - 1);
                }

                int total = getTotalByListSql(sql, params);

                List<T> result;
                if (total == 0) {
                    result = Lists.newArrayList();
                } else {
                    Page<Object> localPage = PageUtils.getLocalPage();
                    if (!StringUtils.isBlank(localPage.getOrderBy())) {
                        sql += " order by " + localPage.getOrderBy();
                    }
                    sql += " limit " + (localPage.getPageNum() - 1) * localPage.getPageSize() + " , " + localPage.getPageSize();
                    result = super.query(sql, new BeanPropertyRowMapper<>(clazz), params);
                }
                page.addAll(result);
                page.setTotal(total);
                return page;
            } finally {
                PageUtils.clearPage();
            }
        }

        return super.query(sql, new BeanPropertyRowMapper<>(clazz), params);
    }

    public int getTotalByListSql(String sql, Object... params) {
        LogUtils.realSqlLog(sql, params);

        String totalSql = sql.toLowerCase();
        int start = totalSql.indexOf("select") + 6;
        int end = totalSql.indexOf("from");
        totalSql = sql.substring(0, start) + " count(*) " + sql.substring(end);

        int total = this.findSingleColumn(totalSql, Integer.class, params);
        return total;
    }

    /**
     * 查找单列集合
     *
     * @param sql
     * @param clazz
     * @param params
     * @param <T>
     * @return
     */
    public <T> List<T> selectSingleColumn(String sql, Class<T> clazz, Object... params) {
        LogUtils.realSqlLog(sql, params);
        return super.query(sql, new SingleColumnRowMapper<>(clazz), params);
    }

    /**
     * 查找单列集合
     *
     * @param sql
     * @param params
     * @return
     */
    public List<String> selectSingleColumn(String sql, Object... params) {
        return selectSingleColumn(sql, String.class, params);
    }

    /**
     * 查找单个字段
     *
     * @param sql
     * @param params
     * @return
     */
    public String findSingleColumn(String sql, Object... params) {
        LogUtils.realSqlLog(sql, params);
        List<String> list = selectSingleColumn(sql, params);
        return CollectionUtils.isEmpty(list) ? null : list.get(0);
    }

    /**
     * 查找单个字段
     *
     * @param sql
     * @param params
     * @return
     */
    public <T> T findSingleColumn(String sql, Class<T> clazz, Object... params) {
        LogUtils.realSqlLog(sql, params);
        List<T> list = selectSingleColumn(sql, clazz, params);
        return CollectionUtils.isEmpty(list) ? null : list.get(0);
    }

    /**
     * 查询双列,第一列作为Key,第二列作为value
     *
     * @param sql
     * @param params
     * @return
     */
    public <K, V> Map<K, V> find2Map(String sql, Object... params) {
        LogUtils.realSqlLog(sql, params);
        Map<K, V> map = Maps.newHashMap();
        SqlRowSet sqlRowSet = super.queryForRowSet(sql, params);
        String[] columnNames = sqlRowSet.getMetaData().getColumnNames();
        if (columnNames.length == 2) {
            while (sqlRowSet.next()) {
                map.put((K) sqlRowSet.getObject(columnNames[0]), (V) sqlRowSet.getObject(columnNames[1]));
            }
        } else {
            while (sqlRowSet.next()) {
                map.put((K) sqlRowSet.getObject(columnNames[0]), null);
            }
        }
        return map;
    }

    public Long insert(String sql, Object... params) {
        LogUtils.realSqlLog(sql, params);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        PreparedStatementCreator psc = con -> {
            PreparedStatement ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            if (!Objects.isNull(params) && params.length != 0) {
                int index = 0;
                for (Object param : params) {
                    ps.setObject(++index, param);
                }
            }
            return ps;
        };
        super.update(psc, keyHolder);
        return Objects.requireNonNull(keyHolder.getKey()).longValue();
    }

    @Override
    public int update(String sql, @Nullable Object... params) throws DataAccessException {
        LogUtils.realSqlLog(sql, params);
        return super.update(sql, newArgPreparedStatementSetter(params));
    }
}
